Overcoming Multi-Level Rollup Field Limitation

Currently I’m working on a project that has some scenarios that need to do rollup calculation in 3 or more level of relationship hierarchy. For a sample and imaginary case, I have an entity called Project that has one or more Project Milestones and each Project Milestones has one or more Invoices (Project 1 – N Project Milestone, Project Milestone 1 – N Invoice). And I would like to see the total paid invoice value at the project level so that the project manager could have a quick overview on the project budget vs invoice that has been paid. To achieve this, the first thing that come up in mind would be the new Rollup Field that introduced in Dynamics CRM 2015.

However, there are some limitations of rollup field: “A rollup attribute formula can’t reference another rollup attribute.” and “Rollup attributes can’t be used as a workflow event or wait condition. These attributes don’t raise the event to trigger workflows.” (https://msdn.microsoft.com/en-us/library/dn817863.aspx)

By looking at the limitations it seems we are a bit hopeless to achieve this in “codeless” way using rollup fields, now let’s develop a plugin for this requirement! Wait, there is a “codeless” solution for this, well at least the code has been wrapped and packaged in this Codeplex solution by Demian Raschkovan: https://calculaterollupfield.codeplex.com/.

So, to trick the limitation of rollup field, I need to create a container (simple field) to hold the rollup value and every time there is any change happened to the source that part of the rollup calculation, trigger a workflow to force calculate the field and copy the value to the hidden field.

Below screenshots are the way I achieve this functionality:

Entity and Field Configuration

• Invoice Entity: A standard currency field

• Project Milestone Entity: A rollup currency field with a hidden currency field as the container.

• Milestone Paid Amount Rollup field Configuration

• Project Entity: A rollup field that referring to the hidden rollup field on project milestone entity and some fields to do the calculation on the remaining budget.

• Project Paid Amount Rollup Configuration:

• Remaining Budget Configuration: A simple calculation from Budget Field subtracted by the Paid Amount

Workflow Configuration

• Workflow on Create/Update of Invoice Entity: This will keep track the rollup on record creation or update of the rollup field. To achieve a seamless multi-level rollup, the rollup field must be calculated first to be stored in the hidden field by utilising Calculate Rollup Fields Custom Workflow Activity from the codeplex link above.

• Workflow on Create/Update of Project Milestone Entity:

The Result

For the sample, I have a sample data of a project with some project milestones related to it:

And then on the first milestone (M1), I created an invoice payment detail:

When I refresh the project record, voila, the value is rolled up and calculated 🙂

Conclusion

With this trick, we could achieve Rollup and Calculation without code. It is a big platform enhancement thank to the CRM Product team that allow us to achieve some basic calculation that previously can only be achieved by using custom code, but now it is simple configurable.

Hope this help!